package com.neuedu.oasystem.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.neuedu.oasystem.dao.IAssetsDao;
import com.neuedu.oasystem.entity.Assets;
import com.neuedu.oasystem.util.DBHelper;
/**
 * 
 * @author LHB
 *
 */
public class AssetsDaoImpl extends BaseDao implements IAssetsDao {
	/**
	 * 添加固定资产
	 */
	@Override
	public boolean add(Assets param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
			String sql = "insert into Assets(Assets_NO,EMP_ID,ASSETS_NAME,ASSETS_NUMBER,ASSETS_PRICE,PURCHASING_AGENT,VERIFIER,PURCHASE_DATE,DEPRECIATION) values(?,?,?,?,?,?,?,?,?)";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, param.getAssetsNo());
			stmt.setInt(2, param.getEmpId());
			stmt.setString(3, param.getAssetsName());
			stmt.setInt(4, param.getAssetsNumber());
			stmt.setDouble(5, param.getAssetsPrice());
			stmt.setString(6, param.getPurchasingAgent());
			stmt.setString(7, param.getVerifier());
			stmt.setTimestamp(8, new java.sql.Timestamp(param.getPurchaseDate().getTime()));
			stmt.setDouble(9, param.getDepreciation());
			int count = stmt.executeUpdate();
			if (count == 1) {
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException();
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}
		return false;
	}
	/**
	 * 修改固定资产
	 */
	@Override
	public boolean edit(Assets param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
			String sql = "update Assets set EMP_ID=?,ASSETS_NAME=?,ASSETS_NUMBER=?,ASSETS_PRICE=?,PURCHASING_AGENT=?,VERIFIER=?,PURCHASE_DATE=?,DEPRECIATIO=? where Assets_no=? ";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(9, param.getAssetsNo());
			stmt.setInt(1, param.getEmpId());
			stmt.setString(2, param.getAssetsName());
			stmt.setInt(3, param.getAssetsNumber());
			stmt.setDouble(4, param.getAssetsPrice());
			stmt.setString(5, param.getPurchasingAgent());
			stmt.setString(6, param.getVerifier());
			stmt.setTimestamp(7, new java.sql.Timestamp(param.getPurchaseDate().getTime()));
			stmt.setDouble(8, param.getDepreciation());
			int count = stmt.executeUpdate();
			if (count == 1) {
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException();
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}
		return false;
	}
	/**
	 * 根据资产标号删除固定资产
	 */
	@Override
	public boolean deleteById(int assetsNo) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;

		try {
			String sql = "delete from Assets where Assets_no=?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, assetsNo);
			int count = stmt.executeUpdate();
			if (count == 1) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}

		return false;
	}
	/**
	 * 根据资产编号查询
	 */
	@Override
	public Assets findById(int no) {
		Connection conn = getConnection();
		PreparedStatement stmt = null;
		Assets assets = null;
		ResultSet rs = null;

		try {

			String sql = "select  EMP_ID=?,ASSETS_NAME=?,ASSETS_NUMBER=?,ASSETS_PRICE=?,PURCHASING_AGENT=?,VERIFIER=?,PURCHASE_DATE=?,DEPRECIATIO=?  where Assets_no=?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, no);
			rs = stmt.executeQuery();
			if (rs.next()) {
				int assetsNo = rs.getInt(1);
				int empId = rs.getInt("EMP_ID");
				String assetsName = rs.getString("ASSETS_NAME");
				int assetsNumber = rs.getInt("ASSETS_NUMBER");
				double assetsPrice = rs.getDouble("ASSETS_PRICE");
				String purchasingAgent = rs.getString("PURCHASING_AGENT");
				String verifier = rs.getString("VERIFIER");
				Date purchaseDate = rs.getTimestamp("PURCHASE_DATE");
				double depreciation = rs.getDouble("DEPRECIATIO");
				assets = new Assets(assetsNo, empId, assetsName, assetsNumber, assetsPrice, purchasingAgent,
						verifier, purchaseDate, depreciation);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, null);
		}
		return assets;
	}
	/**
	 * 查找全部的固定资产
	 */
	@Override
	public List<Assets> findAll() {
		List<Assets> list = new ArrayList<Assets>();
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Assets assets;

		try {
			conn = getConnection();
			String sql = "select Assets_NO,EMP_ID,ASSETS_NAME,ASSETS_NUMBER,ASSETS_PRICE,PURCHASING_AGENT,VERIFIER,PURCHASE_DATE,DEPRECIATION from Assets ";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				int assetsNo = rs.getInt(1);
				int empId = rs.getInt("EMP_ID");
				String assetsName = rs.getString("ASSETS_NAME");
				int assetsNumber = rs.getInt("ASSETS_NUMBER");
				double assetsPrice = rs.getDouble("ASSETS_PRICE");
				String purchasingAgent = rs.getString("PURCHASING_AGENT");
				String verifier = rs.getString("VERIFIER");
				Date purchaseDate = rs.getTimestamp("PURCHASE_DATE");
				double depreciation = rs.getDouble("DEPRECIATION");
				assets = new Assets(assetsNo, empId, assetsName, assetsNumber, assetsPrice, purchasingAgent,
						verifier, purchaseDate, depreciation);
				list.add(assets);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, null);
		}

		return list;
	}

	@Override
	public List<Assets> findByExample(Assets param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		List<Assets> list = new ArrayList<Assets>();
		ResultSet rs = null;
		Assets assets = null;
		try {
			String sql = "select Assets_NO,EMP_ID,ASSETS_NAME,ASSETS_NUMBER,ASSETS_PRICE,PURCHASING_AGENT,VERIFIER,PURCHASE_DATE,DEPRECIATION where Assets_Name like ?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, "%" + param.getAssetsName() + "%");
			rs = stmt.executeQuery();
			while (rs.next()) {
				int assetsNo = rs.getInt(1);
				int empId = rs.getInt("EMP_ID");
				String assetsName = rs.getString("ASSETS_NAME");
				int assetsNumber = rs.getInt("ASSETS_NUMBER");
				double assetsPrice = rs.getDouble("ASSETS_PRICE");
				String purchasingAgent = rs.getString("PURCHASING_AGENT");
				String verifier = rs.getString("VERIFIER");
				Date purchaseDate = rs.getTimestamp("PURCHASE_DATE");
				double depreciation = rs.getDouble("DEPRECIATIO");
				assets = new Assets(assetsNo, empId, assetsName, assetsNumber, assetsPrice, purchasingAgent,
						verifier, purchaseDate, depreciation);
				list.add(assets);
				list.add(assets);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, null);
		}

		return list;
	}

	@Override
	public List<Assets> findByPager(int page, int size) {
		List<Assets> list = new ArrayList<Assets>();
		Connection conn = getConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "select * from"
					+ "(select rownum rn,Assets_NO,EMP_ID,ASSETS_NAME,ASSETS_NUMBER,ASSETS_PRICE,PURCHASING_AGENT,VERIFIER,PURCHASE_DATE,DEPRECIATION from Assets where rownum<=?)where rn>? ";

			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, page * size);
			stmt.setInt(2, (page - 1) * size);
			rs = stmt.executeQuery();
			Assets assets = null;
			while (rs.next()) {
				int assetsNo = rs.getInt(2);
				int empId = rs.getInt("EMP_ID");
				String assetsName = rs.getString("ASSETS_NAME");
				int assetsNumber = rs.getInt("ASSETS_NUMBER");
				double assetsPrice = rs.getDouble("ASSETS_PRICE");
				String purchasingAgent = rs.getString("PURCHASING_AGENT");
				String verifier = rs.getString("VERIFIER");
				Date purchaseDate = rs.getTimestamp("PURCHASE_DATE");
				double depreciation = rs.getDouble("DEPRECIATIO");
				assets = new Assets(assetsNo, empId, assetsName, assetsNumber, assetsPrice, purchasingAgent,
						verifier, purchaseDate, depreciation);
				list.add(assets);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, null);
		}
		return list;
	}

	@Override
	public int deleteByIds(int[] nos) {
		int result = 0;
		Connection conn = getConnection();
		PreparedStatement stmt = null;
		try {
			conn.setAutoCommit(false);
			String sql = "delete from Assets where Assets_no = ?";
			stmt = conn.prepareStatement(sql);
			for (int assetsNo : nos) {
				stmt.setInt(1, assetsNo);
				stmt.addBatch();
			}
			int[] counts = stmt.executeBatch();
			for (int i : counts) {
				if (i == -2) {
					result++;
				}
			}
			if (result == nos.length) {
				conn.commit();
				conn.setAutoCommit(true);
			} else {
				conn.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			if (conn != null) {
				try {
					conn.rollback();
					conn.setAutoCommit(true);
				} catch (SQLException e1) {
					e1.printStackTrace();
				}

			}
		} finally {
			DBHelper.closeAll(null, stmt, null);
		}
		return result;
	}

	@Override
	public int addAll(List<Assets> param) {
		int result = 0;
		Connection conn = getConnection();
		PreparedStatement stmt = null;
		try {
			conn.setAutoCommit(false);
			String sql = "insert into Assets(Assets_NO,EMP_ID,ASSETS_NAME,ASSETS_NUMBER,ASSETS_PRICE,PURCHASING_AGENT,VERIFIER,PURCHASE_DATE,DEPRECIATION) values(?,?,?,?,?,?,?,?,?)";
			stmt = conn.prepareStatement(sql);
			for (Assets i : param) {
				stmt.setInt(1, i.getAssetsNo());
				stmt.setInt(2, i.getEmpId());
				stmt.setString(3, i.getAssetsName());
				stmt.setInt(4, i.getAssetsNumber());
				stmt.setDouble(5, i.getAssetsPrice());
				stmt.setString(6, i.getPurchasingAgent());
				stmt.setString(7, i.getVerifier());
				stmt.setTimestamp(8, new java.sql.Timestamp(i.getPurchaseDate().getTime()));
				stmt.setDouble(9, i.getDepreciation());
				stmt.addBatch();
			}
			int[] counts = stmt.executeBatch();
			for (int i : counts) {
				if (i == 1) {
					result++;
				}
			}
			if (result == param.size()) {
				conn.commit();
				conn.setAutoCommit(true);
			} else {
				conn.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			if (conn != null) {
				try {
					conn.rollback();
					conn.setAutoCommit(true);
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
		} finally {
			DBHelper.closeAll(null, stmt, null);
		}
		return result;
	}

	@Override
	public int editAll(List<Assets> param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		int result = 0;
		try {
			conn.setAutoCommit(false);
			String sql = "update Assets set EMP_ID=?,ASSETS_NAME=?,ASSETS_NUMBER=?,ASSETS_PRICE=?,PURCHASING_AGENT=?,VERIFIER=?,PURCHASE_DATE=?,DEPRECIATIO=? where Assets_no=?";
			stmt = conn.prepareStatement(sql);
			for (Assets i : param) {
				stmt.setInt(9, i.getAssetsNo());
				stmt.setInt(1, i.getEmpId());
				stmt.setString(2, i.getAssetsName());
				stmt.setInt(3, i.getAssetsNumber());
				stmt.setDouble(4, i.getAssetsPrice());
				stmt.setString(5, i.getPurchasingAgent());
				stmt.setString(6, i.getVerifier());
				stmt.setTimestamp(7, new java.sql.Timestamp(i.getPurchaseDate().getTime()));
				stmt.setDouble(8, i.getDepreciation());
				stmt.addBatch();

			}
			int[] counts = stmt.executeBatch();
			for (int i : counts) {
				if (i == 1) {
					result++;
				}
			}
			if (result == param.size()) {
				conn.commit();
				conn.setAutoCommit(true);
			} else {
				conn.rollback();

			}

		} catch (SQLException e) {
			e.printStackTrace();
			if (conn != null) {
				try {
					conn.rollback();
					conn.setAutoCommit(true);
				} catch (SQLException e1) {
					e1.printStackTrace();
				}

			}
		} finally {
			DBHelper.closeAll(null, stmt, null);
		}

		return result;
	}
}
